﻿--获取部门网站列表
CREATE proc [dbo].[proc_SiteForm_GetDepartmentList]
(
	@Department nvarchar(2000),
	@StateId int,
	@CustomerName nvarchar(50),
	@UserName nvarchar(50)
) 
as
	exec
	(
	'declare @temptable table(id int identity(1,1),sid int,departmentid int)'
	+' insert into @temptable(departmentid,sid) select distinct departmentid,sid from SitePersonnel'
	+' select' 
		+' distinct sf.id,'
		+' sf.OrderId,'
		+' sf.ProductId,'
		+' (select title from product p where sf.ProductId=p.Id) as ProductName,'
		+' sf.CustomerId,'
		+' sf.CustomerName,'
		+' sf.ContactPerson,'
		+' sf.ContactTel,'
		+' sf.SignDate,'
		+' sf.WebSitePrice,'
		+' sf.CreateTime,'
		+' sf.StateId,'
		+' (select ss.title from ProjectState ss where ss.ProjectId=sf.stateid) as StateName,'
		+' (select e.ename from employee e where e.username = (select top 1 SitePersonnel.username from SitePersonnel where sf.id = SitePersonnel.sid)) as EName'
	+' from siteform sf left join @temptable sp on sf.id = sp.sid'
	+' where '
		     +' sp.Departmentid in ('+@Department+')'
		     + 'and (case '+ @stateId+'  when 0 then 0 else sf.stateId end) = '+ @stateId
		     +' and sf.CustomerName like ''%'+@CustomerName+'%'''
		     +' and (case '+@UserName+' when 0 then 0 else (select top 1 SitePersonnel.username from SitePersonnel where sf.id = SitePersonnel.sid) end) = '''+@UserName+''''
	)

